"""
FIVEDASH(TM) -- General Purpose Accounting Software
Copyright (C) 2008  Obol Software Pty Ltd

This program is free software: you can redistribute it and/or modify
it. For terms and conditions, see fivedash/license/license.txt

"""

import datetime

from mod_python import apache
from mod_python import Session
from mod_python import util

from phunc import *
import phunc.cx_wrapper.db_tools as db_tools
from phunc.logger import logger

# legacy namespacing
GeneralBusinessErr = exception.GeneralBusinessErr
genErrorPage = error.genErrorPage

##def sessionise_user_portals(access_portals,session):
##	"""
##	Stamps user portals onto session
##	Returns nothing
##	"""
##	portals = []
##	for portal in access_portals:
##		portals.append(portal['name'])
##	session['user_access'] = ','.join(portals)
##	session.save()
class CountryCodeErr(Exception):
	pass
class DataBaseErr(Exception):
	pass
def sessionise_user_portals(access_portals,session):
	"""
	Stamps user portals onto session
	Returns nothing
	"""
	portals = {}
	for portal in access_portals:		
		portals[portal['id']]=portal['name']
	session['user_access'] = portals
	session.save()
def company_group_affiliation(cursor, user_name, company_id, group_id):
	"""
	Checks affiliation to company and group
	Returns a boolean
	"""
	return len(db_tools.exec_action(cursor, 'get_affiliation_info',[user_name, int(company_id), int(group_id)])) > 0

def group_exists(cursor, name):
	"""
	Checks if group exists
	Returns a boolean
	"""
	return len(db_tools.exec_action(cursor, 'get_group_by_name', [name])) > 0
	
def tax_page_updated_today(company_id):
	"""
	Checks if tax page server has been interrogated for updates (stamps date if not - since it is about to be)
	Returns a boolean
	"""
	import datetime
	try:
		have_updated = utils.read_file_content_local(info.LOCAL_TP_INDEX_LOCK+'_'+str(company_id)) == str(datetime.date.today())
	except IOError, e:
		have_updated = False
	if not have_updated:
		utils.save_upload_file(info.tp_save_path, info.LOCAL_TP_INDEX_LOCK+'_'+str(company_id), str(datetime.date.today()))
	return have_updated

#XML namespace for open document table tags
OD_TABLE_NS = "urn:oasis:names:tc:opendocument:xmlns:table:1.0"

def get_text(node):
	"""
	Gets content from an xml element
	Returns a string
	"""
	text = ''
	for child in node.childNodes:
		if child.nodeType == child.ELEMENT_NODE:
			text = text + get_text(child)
		elif child.nodeType == child.TEXT_NODE:
			text = text + child.data
	return text
	
def get_float(node):
	"""
	Gets the office:value attribute from an ods table-cell node
	NOTE: text formatted cells do not have an office:value attribute
	Returns a string
	"""
	return node.getAttribute('office:value')

def update_transaction_tax_page(cx):
	tax_page_changed = False
	
	dbc = cx.cursor()
	country_code = db_tools.exec_action(dbc, 'get_iso_country_code')[0]['code_value']
	current_tax_page_md5 = db_tools.exec_action(dbc, 'get_tax_page_md5')[0]['code_value']
	current_tax_page_id = len(db_tools.exec_action(dbc, 'get_current_tax_page_id')) != 0 and db_tools.exec_action(dbc, 'get_current_tax_page_id')[0]['current_tax_page_id'] or -1
	
	import zipfile
	import xml.dom.minidom
	if current_tax_page_md5 != utils.read_file_content_online(info.TP_INDEX_MD5_ONLINE).split(' ')[0]:
		db_tools.exec_action(dbc, 'update_tax_page_md5', [utils.read_file_content_online(info.TP_INDEX_MD5_ONLINE).split(' ')[0]])
		utils.save_upload_file(info.tp_index_path, info.LOCAL_TP_INDEX, utils.read_file_content_online(info.TP_INDEX_ONLINE))
		
		import phunc.tp_index.TPindex as tp_index
		tax_page_id_list = tp_index.index[country_code].keys()
		tax_page_id_list.sort()
		if int(current_tax_page_id) < int(tax_page_id_list[-1]):
			utils.save_upload_file(info.tp_save_path, info.LOCAL_TP, utils.read_file_content_online(info.tp_server+tp_index.index[country_code][tax_page_id_list[-1]]))
			zip_data = zipfile.ZipFile(info.LOCAL_TP, 'r')
			content = zip_data.read('content.xml')
			content = xml.dom.minidom.parseString(content)
			
			sheet_list = content.getElementsByTagNameNS(OD_TABLE_NS, 'table')
			
			for sheet in sheet_list:
				sheet_name = sheet.getAttributeNS(OD_TABLE_NS, 'name')
				rows = sheet.getElementsByTagNameNS(OD_TABLE_NS, 'table-row')
				if len(rows) >= 3:
					db_tools.exec_action(dbc, 'update_obsolete_tax')
					for i in xrange(2, len(rows)):
						cells = rows[i].getElementsByTagNameNS(OD_TABLE_NS, 'table-cell')
						if len(cells) >= 5:
							if get_text(cells[4]) == '':
								db_tools.exec_action(dbc, 'insert_transaction_tax', [get_text(cells[0]), get_text(cells[1]), int(tax_page_id_list[-1]), get_float(cells[2]),get_text(cells[3]),get_text(cells[3])])
							else:
								db_tools.exec_action(dbc, 'insert_transaction_tax', [get_text(cells[0]), get_text(cells[1]), int(tax_page_id_list[-1]), get_float(cells[2]),get_text(cells[3]),get_text(cells[4])])
							tax_page_changed = True
					db_tools.exec_action(dbc, 'obsolete_user_input_tax')
			#TODO:need a place to let user can change the default tax.
			if db_tools.exec_action(dbc, 'default_tax_is_null')[0]['bool']:
				db_tools.exec_action(dbc,'update_default_tax')
			zip_data.close()
	cx.commit()
	return tax_page_changed
	
def get_sql_suffix_and_page_num(result, page_num = 1, page_size = 5):
	"""
	#TODO: put a comment here
	Returns a dictionary
	"""
	result_dic = {}
	total_result_num = len(result)
	if total_result_num % page_size == 0:
		result_dic['total_page_num'] = total_result_num / page_size
	else:
		result_dic['total_page_num'] = total_result_num / page_size + 1

	if page_num == '' or int(page_num) < 0:
		result_dic['curr_page_num'] = 1
	elif int(page_num) > int(result_dic['total_page_num']):
		result_dic['curr_page_num'] = int(result_dic['total_page_num'])
	else:
		result_dic['curr_page_num'] = int(page_num)
		
	result_dic['page_string'] = " LIMIT %d OFFSET %d" % (int(page_size), (result_dic['curr_page_num']-1)*page_size)
	return result_dic

def file_is_ods_type(file):
	return file.type == 'application/vnd.oasis.opendocument.spreadsheet' or file.type =='application/octet-stream'
	
def create_account_use_file(cx, file):
	"""
	Reads an ods file with inital ledger accounts info, creates ledger accounts as directed
	Returns nothing
	"""
	import zipfile
	import xml.dom.minidom
	utils.save_upload_file((info.accounts_save_path), info.ACCOUNTS_FILE, file.file.read())
	zip_data = zipfile.ZipFile(info.ACCOUNTS_FILE, 'r')
	content = zip_data.read('content.xml')
	content = xml.dom.minidom.parseString(content)
	
	# It is important to search using namespace
	sheet_list = content.getElementsByTagNameNS(OD_TABLE_NS, 'table')
	
	dbc = cx.cursor() 
	for sheet in sheet_list:
		sheet_name = sheet.getAttributeNS(OD_TABLE_NS, 'name')
		rows = sheet.getElementsByTagNameNS(OD_TABLE_NS, 'table-row')
		for row in rows:
			cells = row.getElementsByTagNameNS(OD_TABLE_NS, 'table-cell')
			if len(cells) == 3:
				db_tools.exec_action(dbc,'create_account',['',get_text(cells[0]),get_text(cells[1]),get_text(cells[2]),''])
			elif len(cells) == 4:
				db_tools.exec_action(dbc,'create_account',['',get_text(cells[0]),get_text(cells[1]),get_text(cells[2]),get_text(cells[3])])
	cx.commit()
	zip_data.close()

def printCompany(map,companyN):	
	"""
	#TODO: put a comment here and change function name to standard form
	Returns a string
	"""
	if map.has_key(companyN['id']):
		s="<li><a href='/bin/root.py/display_company_details?company_id=%s'>%s</a>\n"%(companyN['id'],companyN['name'])
		s+="<ul>\n"
		for company in map[companyN['id']]:
			s+=printCompany(map,company)
		s+="</ul>\n"
		s+="</li>"
	else:
		s="<li><a href='/bin/root.py/display_company_details?company_id=%s'>%s</a></li>\n"%(companyN['id'],companyN['name'])
	return s

def access_forbit(req,**params):
		return genErrorPage(err_code=10005)
#**************************************************page function start**************************************************
def loginpage(req, **params):
	"""displays link tiles for companies to which the user has access (redirects to company if there is only one)"""
	page = Page(req, is_root_page=True)
	cursor = get_app_connection().cursor()
	company_group_list = db_tools.exec_action(cursor, 'get_company_group', [page.session['login_user_name'], ''])
	if user_is_root(page.session, cursor) or len(company_group_list)>0:
		page.session['company_group_list'] = company_group_list
	else:
		#raise GeneralBusinessErr('user_no_grant','/bin/login.py/login_out')
		return genErrorPage(err_code=10003)
	page.session['logo_url'] = None
	page.session['company_name'] = None
	page.session.save()
	broadcast_message = params.has_key('broadcast_message') and params['broadcast_message'] or ''
	if not (user_is_root(page.session, cursor) or len(page.session['company_group_list']) > 1):
		util.redirect(req, '/bin/root.py/company?company_id=%d&group_id=%d&broadcast_message=%s' % (int(page.session['company_group_list'][0]['company_id']), int(page.session['company_group_list'][0]['group_id']), broadcast_message))
	page.content['skin'] = page.session['skin']
	page.content['group'] = None
	page.content['company_list'] = []
	if user_is_root(page.session, cursor):
		page.content['group'] = 'root'
	
	#page split start
	pagesize=6
	page_params={}		
	page_num = params.has_key('page') and params['page'] and int(params['page']) or 1
	sql_str = "select company_id, group_id from system.affiliation as af, system.user as us where us.name='%s' and af.user_id=us.id order by user_id" % page.session['login_user_name']
	page_split_setup={
		'cursor':cursor,
		'sql':sql_str,
		'skin':page.session['skin'],
		'page':page_num,
		'pagesize':pagesize,
		'url':"/bin/root.py/loginpage",
		'params':page_params,
		'flag':1,
	}		
	company_groups = table.ControlPage(**page_split_setup)
	company_group_subset = company_groups.result  
	page.content['page_split'] = company_groups.createHtml()
	#page split end

	for item in company_group_subset:
		company_detail = db_tools.exec_action(cursor, 'get_company_name', [int(item['company_id'])])
		if len(company_detail[0]['name']) >20:
			item['company_name'] = company_detail[0]['db_name']
		else:
			item['company_name'] = company_detail[0]['name']
		item['company_logo'] = company_detail[0]['logo_url']
		page.content['company_list'].append(item) 

	#prepare page dictionaries
	page.setup = {
		'breadcrumbs':[page.content['group'] == 'root' and 'Root Menu' or 'Global Menu'],
		'menu_title':page.content['group'] == 'root' and 'Root Menu' or 'Global Menu',
		'menu':page.content['group']=='root' and ['users','groups','grants','spacer','companies'] or None,
		'content_title':'Companies',
		'broadcast_message':broadcast_message,
		'template':'login_company.opt',
	}
	page.info = {
		'query_string':info.query_string,
		'group':page.content['group']
	}
	page.session.save()
	logger.debug(page.session)
	return page.render(debug=True)

def company(req, **params):
	"""downloads tax page if necessary, redirects to additional settings page if necessary, otherwise a portal to which the user has access"""
	try:
		page = Page(req,is_root_page=True,app_db=True)
		#session = Session.Session(req)
		#check_session(req,session)
		session=page.session
		cursor = page.app_db.cursor()

		# 'log in' to the chosen company
		if not company_group_affiliation(cursor, session['login_user_name'], params['company_id'], params['group_id']):
			raise GeneralBusinessErr('access_forbit','/bin/login.py/login_out')
		session['login_company'] = params['company_id']
		session['login_group'] = params['group_id']	
		company_db_info = db_tools.exec_action(cursor, 'get_company_by_id', [int(params['company_id'])])[0]
		session['company_name'] = str(company_db_info['name'])
		session['company_db_ip'] = str(company_db_info['db_ip'])
		session['company_db_name'] = str(company_db_info['db_name'])
		session['company_db_user'] = str(company_db_info['db_user'])
		session['company_db_passwd'] = str(app_db_passwd)
		session['logo_url'] = company_db_info['logo_url']
		access_portals = db_tools.exec_action(cursor, 'get_role_by_group', [int(params['group_id'])])
		if len(access_portals) == 0:
			raise GeneralBusinessErr('usergroup_no_right','/bin/login.py/login_out')
		sessionise_user_portals(access_portals, session)
		session.save()
	
		# check and update tax page
		broadcast_message = ''
		company_db = get_connection(session)
		dbc = company_db.cursor()
		#if 'finance' in session['user_access'].split(','):
		if 'finance' in session['user_access'].values():
			if not tax_page_updated_today(params['company_id']):
				try:
					if update_transaction_tax_page(company_db):
						broadcast_message = 'New tax page found. Transaction taxes for ' +str(session['company_name'])+' have changed'
				except Exception, e:
					broadcast_message = "Tax page not found. Your jurisdiction may not have a tax page, or the tax page server may be unreachable"

			# check for special accounts
			special_params = [{'param_id':'special_ac_cash','param_name':'CASH','param_desc':'CASH'},{'param_id':'special_ac_equip','param_name':'Capital Equipment','param_desc':'Capital Equipment'},{'param_id':'special_ac_comm_pay','param_name':'Commissions Payable','param_desc':'Commissions Payable'},{'param_id':'special_ac_sales_rev','param_name':'Sales Revenue','param_desc':'Sales Revenue'},{'param_id':'special_ac_comm_exp','param_name':'Commissions','param_desc':'Commissions'}]
			special_params_len = len(special_params)
			exist_special_params_len = db_tools.exec_action(dbc, 'exist_special_params_len', [tuple([a['param_id'] for a in special_params])])[0]['special_params_len']
			if special_params_len!=exist_special_params_len or broadcast_message != '':
				util.redirect(req, '/bin/admin.py/additional_setup?broadcast_message=%s' % broadcast_message)

		# check for transaction taxes
		#if 'admin' in session['user_access'].split(','):
		if 'admin' in session['user_access'].values():
			dbc.execute('SELECT * FROM system.transaction_tax')
			if dbc.rowcount == 0:
				util.redirect(req, '/bin/admin.py/additional_setup')
		# if set the portal order
		for order in session['portal_order']:
			for portal in access_portals:
				if portal['id']==order:
					util.redirect(req, '/bin/' + portal['name'] + '.py?broadcast_message=%s' % broadcast_message)
		# proceed to first accessible portal		
		util.redirect(req, '/bin/' + access_portals[0]['name'] + '.py?broadcast_message=%s' % broadcast_message)
	except GeneralBusinessErr,e:
		return genErrorPage(errmsg='%s'%e.getErrMsg(),errurl=e.getURL())
	except Exception,e:
		return genErrorPage( errmsg = "%s" %  e.args )
	except:
		return genErrorPage()

def create_company_page(req, **params):
	"""display a form to create a managed company"""
	page = Page(req, is_root_page=True, superuser_only=True)
	page.setup = {
		'breadcrumbs':['root','Create New Company'],
		'menu_title':'Root Menu',
		'menu':['users','groups','grants','spacer','companies'],
		'content_title':'Create New Company',
		'form'	      :True,
		'template':'create_company.opt',
	}
	page.info = {
		'query_string':info.query_string,
	}
	return page.render()  
    
def create_company_delivery_address(req, **params):
    """display a form to create a managed company"""
    try:
        page = Page(req, is_root_page=True, superuser_only=True)
        session = page.session  
        app_db = page.app_db
        cursor = app_db.cursor()
        root_path = req.document_root()
        logo_url = None
        if params.has_key('logo') and params['logo']!='':
            import os
            fileext = os.path.splitext(params['logo'].filename)[1]
            filevalue = params['logo'].file.read()
            refilename = utils.shorten(params['company_name'])+'_logo'+fileext
            filepath = info.COMPANY_ROOT_PATH + refilename			 
            utils.save_upload_file(info.COMPANY_ROOT_PATH,filepath,filevalue)
            logo_url = os.path.join(info.COMPANY_VIRTUAL_PATH,refilename)
        del(params['logo'])
        session['company_info'] = params
        session['logo_url'] = logo_url
        session.save()
        if params.has_key('address_type'):
            util.redirect(req,'/bin/root.py/create_company')
        page.setup = {
            'breadcrumbs':['root','Create New Company'],
            'menu_title':'Root Menu',
            'menu':['users','groups','grants','spacer','companies'],
            'content_title':'Delivery address',
            'form'	      :True,
            'template':'create_delivery_address.opt',
        }
        page.info = {
            'query_string':info.query_string,
        }
        return page.render()
    except GeneralBusinessErr,e:
        return genErrorPage(errmsg='%s'%e.getErrMsg(),errurl=e.getURL())
    except Exception,e:
        return genErrorPage( errmsg = "%s" % e.args )
    except:
        return genErrorPage()

def create_company(req, **params):
	"""actually create a managed company - redirects to loginpage"""
	try:
		page=Page(req,is_root_page=True,superuser_only=True,app_db=True)
##		session = Session.Session(req)
##		check_session(req,session)
##		app_db = get_app_connection()
##		cursor = app_db.cursor()
##		if not user_is_root(session,cursor): 
##			raise GeneralBusinessErr('access_forbit','/bin/login.py/login_out')
		session = page.session
		app_db = page.app_db
		cursor = app_db.cursor()
		params['short_name'] = session['company_info']['short_name']
		params['company_name'] = session['company_info']['company_name']
		params['business_reg_no'] = session['company_info']['business_reg_no']
		params['line1'] = session['company_info']['line1']
		params['line2'] = session['company_info']['line2']
		params['suburb'] = session['company_info']['suburb']
		params['region'] = session['company_info']['region']
		params['code'] = session['company_info']['code']
		params['country'] = session['company_info']['country']
		logo_url = session['logo_url']
		if session['company_info'].has_key('address_type'):
			params['pob_name'] = session['company_info']['pob_name']
			params['pob_phone'] = session['company_info']['pob_phone']
			params['pob_fax'] = session['company_info']['pob_fax']
		temp_dic = {}
		info._set_autocommit(app_db)
		try:
			cursor.execute("CREATE DATABASE %s" % utils.escape_quotes_in_sql(utils.shorten(params['short_name'])))
			company_id = int(db_tools.exec_action(cursor, 'get_company_id')[0]['id'])
			if logo_url:
				cursor.execute("INSERT INTO system.company (id, name, db_ip, db_name, db_user, db_passwd,logo_url) VALUES (%d, '%s', '%s','%s','%s',encrypt('%s','','bf'),'%s')" % (company_id, utils.escape_quotes_in_sql(params['company_name']), 'localhost', utils.escape_quotes_in_sql(utils.shorten(params['short_name'])), app_db_user, app_db_passwd,logo_url))
			else:
				cursor.execute("INSERT INTO system.company (id, name, db_ip, db_name, db_user, db_passwd) VALUES (%d, '%s', '%s','%s','%s',encrypt('%s','','bf' ))" % (company_id, utils.escape_quotes_in_sql(params['company_name']), 'localhost', utils.escape_quotes_in_sql(utils.shorten(params['short_name'])), app_db_user, app_db_passwd))
			cursor.execute("INSERT INTO system.affiliation VALUES (%d, %d, (SELECT id FROM system.group WHERE name = 'root'))" % (int(session['login_user_id']), int(company_id)))
			country_code = db_tools.exec_action(cursor, 'get_country_code', [params['country'],params['country']])
			if country_code:
				country_code = country_code[0]['code']
			else:
				raise CountryCodeErr
		except CountryCodeErr,e:
			broadcast_message="Create company '%s' failed.The country does not exist!"%utils.shorten(params['short_name'])
			try:
				cursor.execute("DROP DATABASE %s" % utils.shorten(params['short_name']))
				if company_id:
					cursor.execute("DELETE FROM system.company WHERE id= %s" %str(company_id))
					cursor.execute("DELETE FROM system.affiliation WHERE user_id= %s AND company_id=%s AND group_id=(SELECT id FROM system.group WHERE name = 'root')" % (session['login_user_id'],company_id))
			except:
				app_db.close()
			util.redirect(req,'/bin/root.py/loginpage?broadcast_message=%s'%broadcast_message)
		except Exception,e:
			broadcast_message="Create company '%s' failed.Database error,please contact System Administator"%utils.shorten(params['short_name'])
			try:
				cursor.execute("DROP DATABASE %s" % utils.shorten(params['short_name']))
				if company_id:
					cursor.execute("DELETE FROM system.company WHERE id= %s" %str(company_id))
					cursor.execute("DELETE FROM system.affiliation WHERE user_id= %s AND company_id=%s AND group_id=(SELECT id FROM system.group WHERE name = 'root')" % (session['login_user_id'],company_id))
			except:
				app_db.close()
			util.redirect(req,'/bin/root.py/loginpage?broadcast_message=%s'%broadcast_message)
		finally:
			app_db.close()
		#operate company
		try:			
			new_db_conn = get_app_connection(dbname=utils.shorten(params['short_name']))			
			new_db_cursor = new_db_conn.cursor()
			somevoidthing = db_tools.exec_action(new_db_cursor, 'create_init_db_func')
			new_db_cursor.execute("SELECT init_db()")
			new_db_cursor.execute("INSERT INTO system.entity (type, name) VALUES ('company', '%s')" % utils.escape_quotes_in_sql(params['company_name']))
			if params.has_key('business_reg_no') and params['business_reg_no']:
				new_db_cursor.execute("INSERT INTO system.contact_detail (entity_id, type, detail) VALUES (1, 'abn', '%s')" % utils.escape_quotes_in_sql(params['business_reg_no']))
			if country_code==info.ISO_COUNTRY_CODE_AUSTRALIA:
				new_db_cursor.execute("INSERT INTO system.sys_code (code_id, code_value) VALUES ('%s', '%s')" % (info.BUSINESS_NUM_LABEL,info.BUSINESS_NUM_LABEL_AUSTRALIA))
			else:
				new_db_cursor.execute("INSERT INTO system.sys_code (code_id, code_value) VALUES ('%s', '%s')" %(info.BUSINESS_NUM_LABEL,info.BUSINESS_NUM_LABEL_OTHER))

			if params.has_key('line2'):
				new_db_cursor.execute("INSERT INTO system.address (line1, line2, suburb, region, code, country) VALUES ('%s','%s','%s','%s','%s','%s')" % (utils.escape_quotes_in_sql(params['line1']), utils.escape_quotes_in_sql(params['line2']), utils.escape_quotes_in_sql(params['suburb']), utils.escape_quotes_in_sql(params['region']), params['code'], params['country']))
			else:
				new_db_cursor.execute("INSERT INTO system.address (line1, suburb, region, code, country) VALUES ('%s','%s','%s','%s','%s')" % (utils.escape_quotes_in_sql(params['line1']), utils.escape_quotes_in_sql(params['suburb']), utils.escape_quotes_in_sql(params['region']), params['code'], params['country'])) 
				
			if session['company_info'].has_key('address_type'):
				new_db_cursor.execute("INSERT INTO system.location(entity_id,name,phone,fax,address_id) VALUES(1,'%s','%s','%s',1)"%(utils.escape_quotes_in_sql(params['pob_name']),params['pob_phone'],params['pob_fax']))
			else:
				if params.has_key('line2'):
					new_db_cursor.execute("INSERT INTO system.address (line1, line2, suburb, region, code, country) VALUES ('%s','%s','%s','%s','%s','%s')" % (utils.escape_quotes_in_sql(params['l_line1']), utils.escape_quotes_in_sql(params['l_line2']), utils.escape_quotes_in_sql(params['l_suburb']), utils.escape_quotes_in_sql(params['l_region']), params['l_code'], params['l_country']))
				else:
					new_db_cursor.execute("INSERT INTO system.address (line1, suburb, region, code, country) VALUES ('%s','%s','%s','%s','%s')" % (utils.escape_quotes_in_sql(params['l_line1']), utils.escape_quotes_in_sql(params['l_suburb']), utils.escape_quotes_in_sql(params['l_region']), params['l_code'], params['l_country']))
				new_db_cursor.execute("INSERT INTO system.location(entity_id,name,phone,fax,address_id) VALUES(1,'%s','%s','%s',2)"%(utils.escape_quotes_in_sql(params['pob_name']),params['pob_phone'],params['pob_fax']))
			
			new_db_cursor.execute("INSERT INTO system.sys_code (code_id, code_value) VALUES ('this_entity_id', '1')")
			new_db_cursor.execute("INSERT INTO system.sys_code (code_id, code_value) VALUES ('this_address_id', '1')")
			new_db_cursor.execute("INSERT INTO system.sys_code (code_id, code_value) VALUES ('iso_country_code', '%s')" % country_code)
			new_db_cursor.execute("DROP FUNCTION init_db()")
			new_db_conn.commit()
			new_db_conn.close()
		except DataBaseConnErr,e:
			broadcast_message="Create company '%s' failed.Can't connect to the database."%utils.shorten(params['short_name'])
			try:			
				app_db = get_app_connection()
				cursor = app_db.cursor()
				info._set_autocommit(app_db)
				cursor.execute("DROP DATABASE %s" % utils.shorten(params['short_name']))
				cursor.execute("DELETE FROM system.company WHERE id= %s" %str(company_id))
				cursor.execute("DELETE FROM system.affiliation WHERE user_id= %s AND company_id=%s AND group_id=(SELECT id FROM system.group WHERE name = 'root')" % (session['login_user_id'],company_id))
				app_db.close()				
			except DataBaseConnErr,e:
				pass
			except Exception,e:
				app_db.close()		
			util.redirect(req,'/bin/root.py/loginpage?broadcast_message=%s'%broadcast_message)
		except Exception,e:		
			broadcast_message="Create company '%s' failed.Database error,please contact System Administator"%utils.shorten(params['short_name'])
			try:				
				app_db = get_app_connection()
				cursor = app_db.cursor()
				info._set_autocommit(app_db)
				cursor.execute("DROP DATABASE %s" % utils.shorten(params['short_name']))
				cursor.execute("DELETE FROM system.company WHERE id= %s" %str(company_id))
				cursor.execute("DELETE FROM system.affiliation WHERE user_id= %s AND company_id=%s AND group_id=(SELECT id FROM system.group WHERE name = 'root')" % (session['login_user_id'],company_id))
				app_db.close()
				new_db_conn.close()
			except DataBaseConnErr,e:
				new_db_conn.close()
			except Exception,e:
				app_db.close()
				new_db_conn.close()
			util.redirect(req,'/bin/root.py/loginpage?broadcast_message=%s'%broadcast_message)
		temp_dic['company_id'] = company_id
		temp_dic['group_id'] = 1
		session['company_group_list'].append(temp_dic)
		session['company_db_ip'] = str(app_db_ip)
		session['company_db_name'] = str(utils.shorten(params['short_name']))
		session['company_db_user'] = str(app_db_user)
		session['company_db_passwd'] = str(app_db_passwd)
		session.save()	
		util.redirect(req, '/bin/root.py/upload_accounts_file_page?company_id=%d' % company_id)
	except GeneralBusinessErr,e:
		return genErrorPage(errmsg='%s'%e.getErrMsg(),errurl=e.getURL())
	except Exception,e:
		return genErrorPage( errmsg = "%s" % e.args )
	except:
		return genErrorPage()

def upload_accounts_file_page(req, **params):
	"""display a form for uploading an initial chart of accounts"""
	page = Page(req, company_db=True, is_root_page=True, superuser_only=True)
	company_cursor = page.company_db.cursor()
	page.content = {
		'company_id':params['company_id'],
		'iso_country_code':db_tools.exec_action(company_cursor, 'get_iso_country_code')[0]['code_value'],
		'sample_files':[],
		'generic_sample_files':[],
		'broadcast_message':params.has_key('broadcast_message') and params['broadcast_message'] or 'Company created'
	}
	import os, re
	pattern = '.*[.](?=ods$).*$'
	try:
		for li in os.listdir(req.document_root()[:req.document_root().rindex('skins')] +'samples/'+ str(page.content['iso_country_code']) + '/'):
			if re.search(pattern, li):
				page.content['sample_files'].append(li)
	except:
		pass
	finally:
		for li in os.listdir(req.document_root()[:req.document_root().rindex('skins')] +'samples/generic/'):
			if re.search(pattern, li):
				page.content['generic_sample_files'].append(li)
	page.setup = {
		'breadcrumbs':['root','Create New Company - Step 2'],
		'menu_title':'Root Menu',
		'menu':['users','groups','grants','spacer','companies'],
		'content_title':'Create New Company - Step 2',
		'broadcast_message':page.content['broadcast_message'],
		'template':'upload_accounts_file.opt',
		'form':True,
	}
	page.info = {
		'query_string':info.query_string
	}
	return page.render()

def create_accounts(req, **params):
	"""actually create accounts from an uploaded file - redirects to loginpage"""
	try:
		page=Page(req,is_root_page=True,superuser_only=True,app_db=True)
		session = page.session
		enzhun_conn = page.app_db
		enzhun_cursor = enzhun_conn.cursor()
		if not user_is_root(session,enzhun_cursor): raise GeneralBusinessErr('access_forbit','/bin/login.py/login_out')
		company_db_info = db_tools.exec_action(enzhun_cursor, 'get_company_by_id', [int(params['company_id'])])[0]
		enzhun_conn.close()
		#company_conn = psycopg2.connect('host=%s dbname=%s user=%s password=%s' %(str(company_db_info['db_ip']), str(company_db_info['db_name']), app_db_user,app_db_passwd))
		company_conn = get_app_connection(host=str(company_db_info['db_ip']),dbname=str(company_db_info['db_name']))
		if not file_is_ods_type(params['file']):
			broadcast_message = 'File was the wrong type. Please upload an open document spreadsheet (.ods) file'
			util.redirect(req, '/bin/root.py/upload_accounts_file_page?company_id=%s&broadcast_message=%s' % (params['company_id'],broadcast_message))
			return
		else:
			create_account_use_file(company_conn,params['file'])
			broadcast_message = 'Company created'
		company_conn.close()
		util.redirect(req, '/bin/root.py/loginpage?broadcast_message=%s&from=create_company' % broadcast_message)
	except GeneralBusinessErr,e:
		return genErrorPage( errmsg = "%s" %  e.getErrMsg() )
	except Exception,e:
		return genErrorPage( errmsg = "%s" % str(e.args) )
	except:
		return genErrorPage()
	
def create_user(req, **params):
	"""actually creates a user - redirects to login page"""		
	try:
		page =Page(req, app_db=True, superuser_only=True,is_root_page=True)
		conn = page.app_db
		import phunc.user as user
		user_id, broadcast_message = user.createUser(conn, params['user_name'], params['user_pwd'])
		util.redirect(req, "/bin/root.py/user_management?broadcast_message=%s. You need to <a href='/bin/root.py/grant_right_page?user_id=%s'>grant rights</a> to set the user's access" % (broadcast_message,user_id))
	except GeneralBusinessErr,e:
		return genErrorPage(errmsg='%s'%e.getErrMsg(),errurl=e.getURL())
	except Exception,e:
		return genErrorPage( errmsg = "%s" % e.args )
	except:
		return genErrorPage() 
##	try:
##		session = Session.Session(req)
##		check_session(req,session)	
##		conn = get_app_connection( )
##		cursor = conn.cursor()
##		if not user_is_root(session,cursor): raise GeneralBusinessErr('access_forbit','/bin/login.py/login_out')
##		user_id, broadcast_message = user.createUser(conn, params['user_name'], params['user_pwd'])
##		util.redirect(req, "/bin/login.py/user_management?broadcast_message=%s. You need to <a href='/bin/login.py/grant_right_page?user_id=%s'>grant rights</a> to set the user's access" % (broadcast_message,user_id))
##	except GeneralBusinessErr,e:
##		return genErrorPage(errmsg='%s'%e.getErrMsg(),errurl=e.getURL())
##	except Exception,e:
##		return genErrorPage( errmsg = "%s" % e.args )
##	except:
##		return genErrorPage()    

def create_group(req, **params):
#	"""actually creates a group - redirects to login page"""
	page = Page(req, is_root_page=True, superuser_only=True)
	conn = page.app_db
	cursor = conn.cursor()
	if group_exists(cursor, params['group_name']):
		util.redirect(req, "/bin/root.py/group_management?broadcast_message=this group name '%s' already exists!"%params['group_name'])
	group_id = int(db_tools.exec_action(cursor, 'get_group_id')[0]['id'])
	db_tools.exec_action(cursor, 'insert_group', [group_id, params['group_name']])
	if type(params['role_id']) == type([]):
		for role_id in params['role_id']:
			db_tools.exec_action(cursor, 'config_grant', [group_id, int(role_id)])
	else:
		db_tools.exec_action(cursor, 'config_grant', [group_id, int(params['role_id'])])
	conn.commit()
	util.redirect(req, "/bin/root.py/group_management?broadcast_message=Group '%s' Created"%params['group_name'])
##	try:
##		session = Session.Session(req)
##		check_session(req,session)	
##		conn = get_app_connection()
##		cursor = conn.cursor()
##		if not user_is_root(session,cursor): raise GeneralBusinessErr('access_forbit','/bin/login.py/login_out')
##		if group_exists(cursor, params['group_name']):
##			util.redirect(req, "/bin/login.py/group_management?broadcast_message=this group name '%s' already exists!"%params['group_name'])
##		group_id = int(db_tools.exec_action(cursor, 'get_group_id')[0]['id'])
##		db_tools.exec_action(cursor, 'insert_group', [group_id, params['group_name']])
##		if type(params['role_id']) == type([]):
##			for role_id in params['role_id']:
##				db_tools.exec_action(cursor, 'config_grant', [group_id, int(role_id)])
##		else:
##			db_tools.exec_action(cursor, 'config_grant', [group_id, int(params['role_id'])])
##		conn.commit()
##		util.redirect(req, "/bin/login.py/group_management?broadcast_message=Group '%s' Created"%params['group_name'])
#	except GeneralBusinessErr,e:
#		return genErrorPage(errmsg='%s'%e.getErrMsg(),errurl=e.getURL())
#	except Exception,e:
#		return genErrorPage( errmsg = "%s" % e.args )
#	except:
#		return genErrorPage()

def user_management(req, **params):
	"""display a form for adding/deleting/editing users"""
	page = Page(req, is_root_page=True, superuser_only=True)
	cursor = page.app_db.cursor()
	broadcast_message = params.has_key('broadcast_message') and params['broadcast_message'] or ''
	check_users = db_tools.exec_action(cursor, 'check_user_grant')
	pagesize=6
	page_num = params.has_key('page') and params['page'] and int(params['page']) or 1
	sql_str = "SELECT * FROM system.user WHERE is_active='TRUE' ORDER BY id"
	page_split_setup={
		'cursor':cursor,
		'sql':sql_str,
		'skin':page.session['skin'],
		'page':page_num,
		'pagesize':pagesize,
		'url':"/bin/root.py/user_management",
		'params':{},
	}		
	users = table.ControlPage(**page_split_setup)
	for user in users.result:
		if utils.is_root_user(user['id'], cursor):
			user['is_root']=True
		else:
			user['is_root']=False
	user_list = users.result
	page_split = users.createHtml()
	form_setup={
		'id':'user_details',
		'action':'/bin/root.py/create_user',
		'onsubmit':'check_form()',
		'fieldsets':[{
			'id':None,
			'legend':'New User Details',
			'classid':'form',
			'items':[
				{'type':'text','label':'User Name','id':'user_name','name':'user_name','classid':'required validate-chars1'},		    
				{'type':'password','label':'User Password','id':'user_pwd','name':'user_pwd','classid':'required validate-chars1'},
				{'type':'password','label':'Retype User Password','id':'config_user_pwd','name':'config_user_pwd','classid':'required validate-chars1'}		   
			]
		}],
		'template_dir':info.getTemplateDir(req),
		'form_bottom': {'type':'div','id':None,'classid':'submit','style':None,'items':[{'type':'submit','id':'submit','name':'submit','value':'Create'}]}
	}
	uform = form.user_form(**form_setup)
	page.content = {
		'check_users':check_users,
		'user_list':user_list,
		'uform':uform,
		'page_split':page_split,
		'page':page, 
		'broadcast_message':broadcast_message
	}
	page.setup = {
		'breadcrumbs':['root','Manage Users'],
		'menu_title':'Root Menu',
		'menu':['users!','groups','grants','spacer','companies'],
		'content_title':'Manage Users',
		'broadcast_message':broadcast_message,
		'template':'user_manage.opt',
		'form':True,
        'javascript':True
	}
	page.info = {
		'query_string':info.query_string
	}
	return page.render()	      

def edit_group_page(req, **params):
	"""displays a form for editing a group"""
	page = Page(req, is_root_page=True, superuser_only=True)
	cursor = page.app_db.cursor()
	group_details = db_tools.exec_action(cursor, 'get_group_details', [params['group_id']])[0] #this may not be necessary - check it
	page.content = {
		'group_id':params['group_id'],
		'role_list':db_tools.exec_action(cursor, 'get_all_role'),
		'grant_list':db_tools.exec_action(cursor, 'get_all_grant'),
		'group_list':db_tools.exec_action(cursor, 'get_all_group', [''])
	}
	page.setup = {
		'breadcrumbs':['root','groups',group_details['name'].capitalize()+ ' - Edit'],
		'menu_title':'Root Menu',
		'menu':['users','groups!','grants','spacer','companies'],
		'content_title':group_details['name'].capitalize(),
		'template':'edit_group.opt',
		'form':True,
        'javascript':True
	}
	page.info = {
		'query_string':info.query_string,
	}
	return page.render()

def edit_group(req,**params):
	"""actually updates group details, redirects to group management page"""
	try:
		page=Page(req,is_root_page=True,superuser_only=True,app_db=True)
		session=page.session
		conn=page.app_db
		cursor=conn.cursor()
##		session = Session.Session(req)
##		check_session(req,session)	
##		conn = get_app_connection( )
##		cursor = conn.cursor()
##		if not user_is_root(session,cursor): raise GeneralBusinessErr('access_forbit','/bin/login.py/login_out')
		group_id,group_name = params['group_id'].split(':')
		db_tools.exec_action(cursor, 'delete_grant_of_group', [group_id])
		if type(params['role_id']) == type([]):	
			for role_id in params['role_id']:
				db_tools.exec_action(cursor, 'config_grant', [int(group_id), int(role_id)])
		else:
			db_tools.exec_action(cursor, 'config_grant', [int(group_id), int(params['role_id'])])
		conn.commit()
		util.redirect(req, "/bin/root.py/group_management?broadcast_message=Group '%s' Edited"%group_name)
	except GeneralBusinessErr,e:
		return genErrorPage(errmsg='%s'%e.getErrMsg(),errurl=e.getURL())
	except Exception,e:
		return genErrorPage( errmsg = "%s" % e.args )
	except:
		return genErrorPage()

def display_group_details(req,**params):
	"""displays a detail page for a single group"""
	page = Page(req, is_root_page=True, superuser_only=True)
	cursor = page.app_db.cursor()
	group_id = params['group_id']
	group_grant_details = db_tools.exec_action(cursor, 'get_group_grant_details', [group_id])
	grant_str = ''
	if group_grant_details:
		for grant in group_grant_details:
			grant_str += grant['role_name']+' , '
		grant_str = grant_str[:-2]
	page.content = {
		'grant_str':grant_str,
		'group_details':db_tools.exec_action(cursor, 'get_group_details', [group_id])[0],
	}
	list_setup={
		'classid':{'main':'title title2','detail':'table'},
		'template_dir':info.getTemplateDir(req),
		'detail':{'title':['Group Id','Group Name','Group Roles'],'value':[page.content['group_details']['id'],page.content['group_details']['name'],grant_str]},
		'main':' ' # space required for formatting
	}
	ulist = form.user_div(**list_setup)
	page.content['ulist']=ulist
	page.setup = {
		'breadcrumbs':['root','groups',page.content['group_details']['name'].capitalize() + ' - Details'],
		'menu_title':'Root Menu',
		'menu':['users','groups!','grants','spacer','companies'],
		'content_title':page.content['group_details']['name'].capitalize() + ' - Details',    
		'template':'group_details.opt'
	}
	page.info = {
		'query_string':info.query_string
	}
	return page.render()

def group_management(req, **params):
	"""displays a group list and form for adding a new group"""
	page = Page(req, is_root_page=True, superuser_only=True)
	cursor = page.app_db.cursor()
	broadcast_message = params.has_key('broadcast_message') and params['broadcast_message'] or ''
	check_groups = db_tools.exec_action(cursor, 'check_group_grant')
	page_num = params.has_key('page') and params['page'] and int(params['page']) or 1
	page_split_setup={
		'cursor':cursor,
		'sql':"select * from system.group order by id",
		'skin':page.session['skin'],
		'page':page_num,
		'pagesize':6,
		'url':"/bin/root.py/group_management",
		'params':{}
	}		
	groups = table.ControlPage(**page_split_setup)
	group_list = groups.result
	page_split = groups.createHtml()
	role_list = db_tools.exec_action(cursor, 'get_all_role')
	form_setup={
		'id':'group_details',
		'action':'/bin/root.py/create_group',
		'onsubmit':'check_right()',
		'fieldsets':[{
			'id':None,
			'legend':'Create New Group',
			'classid':'form',
			'items':[
				{'type':'text','label':'Group Name','id':'group_name','name':'group_name','classid':'required validate-chars1'},		    
				{'type':'checkbox','checkbox_type':2,'checkbox_value':role_list,'whole_label':'Roles','label':'name','id':'id','name':'role_id','value':'id','format':1,'prefix':True,'prefix_text':'role_'},
			]
		}],
		'template_dir':info.getTemplateDir(req),
		'form_bottom': {'type':'div','id':None,'classid':'bottom input','style':None,'items':[{'type':'submit','id':'submit','name':'submit','value':'Create'}]}
	}
	uform = form.user_form(**form_setup)
	page.content = {
		'uform':uform,
		'group_list':group_list,
		'role_list':role_list,	    
		'page_split':page_split,
		'page':page_num, 
		'broadcast_message':broadcast_message,
		'check_groups':check_groups
	}	   
	page.setup = {
		'breadcrumbs':['root','Manage Groups'],
		'menu_title':'Root Menu',
		'menu':['users','groups!','grants','spacer','companies'],
		'content_title':'Manage Groups',
		'broadcast_message':broadcast_message,
		'template':'group_manage.opt',
		'form':True,
        'javascript':True
	}
	page.info = {
		'query_string':info.query_string
	}
	return page.render()

def delete_group(req,**params):
	"""actually deletes a group, redirects to group management page"""
	try:
		page=Page(req,is_root_page=True,superuser_only=True,app_db=True)
##		session = Session.Session(req)
##		check_session(req,session)	
##		conn = info.getAppConnection( )
##		cursor = conn.cursor()
##		if not user_is_root(session,cursor): raise GeneralBusinessErr('access_forbit','/bin/login.py/login_out')
		broadcast_message = ''
		group_id = params['group_id']
		group_name = params['group_name']
		if group_name.lower()!='root':
			db_tools.exec_action(page.app_db.cursor(), 'delete_group', [params['group_id']])
			page.app_db.commit()
			broadcast_message = "group '%s' deleted"%params['group_name']
		else:
			broadcast_message = "root can't be deleted"
		util.redirect(req,"/bin/root.py/group_management?broadcast_message=%s"%broadcast_message)
	except GeneralBusinessErr,e:
		return genErrorPage(errmsg='%s'%e.getErrMsg(),errurl=e.getURL())
	except Exception,e:
		return genErrorPage( errmsg = "%s" % e.args )
	except:
		return genErrorPage() 

def grant_right_page(req, **params):
	"displays a user list and form for granting rights"""
	page = Page(req, is_root_page=True, superuser_only=True)
	cursor = page.app_db.cursor()
	user_id = None
	if params.has_key('user_id') and params['user_id']:
		user_id = int(params['user_id'])
	broadcast_message = params.has_key('broadcast_message') and params['broadcast_message'] or ''
	page_num = params.has_key('page') and params['page'] and int(params['page']) or 1
	sql_str = "SELECT a.user_id,u.name AS user_name,a.company_id,c.name AS company_name,a.group_id,g.name AS group_name FROM system.affiliation a LEFT JOIN system.user u ON a.user_id=u.id LEFT JOIN system.company c ON a.company_id=c.id LEFT JOIN system.group g ON a.group_id=g.id WHERE a.company_id<>0 and a.user_id not in (select id from system.user where is_active='FALSE') ORDER BY a.user_id,a.company_id,a.group_id"
	page_split_setup={
		'cursor':cursor,
		'sql':sql_str,
		'skin':page.session['skin'],
		'page':page_num,
		'pagesize':8,
		'url':"/bin/root.py/grant_right_page",
		'params':{}
	}		
	user_rights = table.ControlPage(**page_split_setup)
	user_rights_list = user_rights.result
	page_split = user_rights.createHtml()
	user_list = db_tools.exec_action(cursor, 'get_users', [''])
	for user in user_list:
		if utils.is_root_user(user['id'], cursor):
			user['is_root']=True
		else:
			user['is_root']=False		
	company_list = db_tools.exec_action(cursor, 'get_all_company')
	group_list = db_tools.exec_action(cursor, 'get_all_group', [''])
	form_setup={
		'id':'grant_right',
		'action':'/bin/root.py/grant_right',
		'fieldsets':[{
			'id':None,
			'legend':'Grant Details',
			'classid':'form',
			'items':[
				{'type':'select','label':'User Name','id':'user_id','name':'user_id','classid':'required','option':user_list,'option_value':{'split':':','items':['id','name'],'default_value':''},'option_text':{'split':None,'items':['name'],'default_text':'-- Please select --'},'selected_type':1,'selected_value':'','selected_key':None},
				{'type':'select','label':'Company Name','id':'company_id','name':'company_id','classid':'required','option':company_list,'option_value':{'split':':','items':['id','name'],'default_value':''},'option_text':{'split':None,'items':['name'],'default_text':'-- Please select --'},'selected_type':1,'selected_value':'','selected_key':None},
				{'type':'select','label':'Group Name','id':'group_id','name':'group_id','classid':'required','option':group_list,'option_value':{'split':':','items':['id','name'],'default_value':''},'option_text':{'split':None,'items':['name'],'default_text':'-- Please select --'},'selected_type':1,'selected_value':'','selected_key':None},			       
			]
		}],
		'template_dir':info.getTemplateDir(req),
		'form_bottom': {'type':'div','id':None,'classid':'submit','style':None,'items':[{'type':'submit','id':'submit','name':'submit','value':'Add'}]},
	}
	uform = form.user_form(**form_setup)
	page.content = {
		'uform':uform,
		'user_id':user_id,
		'user_rights_list':user_rights_list,
		'user_list':user_list,
		'company_list':company_list,
		'group_list':group_list,
		'page_split':page_split,
		'page':page_num, 
		'broadcast_message':broadcast_message
	}
	page.setup = {
		'breadcrumbs':['root','Grant User Rights'],
		'menu_title':'Root Menu',
		'menu':['users','groups','grants!','spacer','companies'],
		'content_title':'Grant User Rights',
		'broadcast_message':broadcast_message,
		'template':'grant_right.opt',
		'form':True
	}
	page.info = {
		'query_string':info.query_string
	}
	return page.render()

def grant_right(req, **params):
	"""actually grants rights to a user, redirects to grant rights page"""
	try:
		page=Page(req,is_root_page=True,superuser_only=True,app_db=True)
		conn=page.app_db
		cursor=conn.cursor()
##		session = Session.Session(req)
##		check_session(req,session)	
##		conn = get_app_connection()
##		cursor = conn.cursor()
##		if not user_is_root(session,cursor): raise GeneralBusinessErr('access_forbit','/bin/login.py/login_out')
		broadcast_message = ''
		user_id,user_name = params['user_id'].split(':')
		company_id,company_name = params['company_id'].split(':')
		group_id,group_name = params['group_id'].split(':')
		same_rights = db_tools.exec_action(cursor, 'user_have_right', [int(user_id), int(company_id)])
		if same_rights:
			broadcast_message="error:%s's can not have more than one role on '%s'!"%(user_name,company_name)	    
		else:
			db_tools.exec_action(cursor, 'assign_user', [int(user_id), int(company_id), int(group_id)])
			conn.commit()
			broadcast_message="Right: %s granted to %s on %s"%(group_name,user_name,company_name)
		util.redirect(req,"/bin/root.py/grant_right_page?broadcast_message=%s"%broadcast_message)	    
	except GeneralBusinessErr,e:
		return genErrorPage(errmsg='%s'%e.getErrMsg(),errurl=e.getURL())
	except Exception,e:
		return genErrorPage( errmsg = "%s" % e.args )
	except:
		return genErrorPage()

def remove_right(req, **params):
	"""actually removes the specified rights, redirects to grant rights page"""
	try:
		page=Page(req,is_root_page=True,superuser_only=True,app_db=True)
		conn=page.app_db
		cursor=conn.cursor()
##		session = Session.Session(req)
##		check_session(req,session)	
##		conn = get_app_connection()
##		cursor = conn.cursor()
##		if not user_is_root(session,cursor): raise GeneralBusinessErr('access_forbit','/bin/login.py/login_out')
		broadcast_message = ''
		user_id = params['user_id']
		user_name = params['user_name'] 
		company_id = params['company_id']
		company_name = params['company_name']
		group_id = params['group_id']
		group_name = params['group_name']
		if not utils.is_root_user(user_id, cursor):
			db_tools.exec_action(cursor, 'revoke_user', [int(user_id), int(company_id), int(group_id)])
			conn.commit()
			broadcast_message="removes %s's '%s' rights for company '%s'" % (user_name, group_name,company_name)
		else:
			broadcast_message = "root privileges can't be removed"
		util.redirect(req,"/bin/root.py/grant_right_page?broadcast_message=%s"%broadcast_message)	    
	except GeneralBusinessErr,e:
		return genErrorPage(errmsg='%s'%e.getErrMsg(),errurl=e.getURL())
	except Exception,e:
		return genErrorPage( errmsg = "%s" % e.args )
	except:
		return genErrorPage()

def delete_user(req, **params):
	"""actually deletes a user, redirects to user management page"""
	try:
		page=Page(req,is_root_page=True,superuser_only=True,app_db=True)
		conn=page.app_db
		cursor=conn.cursor()
##		session = Session.Session(req)
##		check_session(req,session)	
##		conn = get_app_connection()
##		cursor = conn.cursor()
##		if not user_is_root(session,cursor): raise GeneralBusinessErr('access_forbit','/bin/login.py/login_out')
		broadcast_message = ''
		user_id = params['user_id']
		user_name = params['user_name'] 
		if not utils.is_root_user(user_id, cursor):
			db_tools.exec_action(cursor, 'remove_right_on_user', [user_id])
			db_tools.exec_action(cursor, 'delete_user', [user_id])
			conn.commit()
			broadcast_message="User '%s' has been removed!"%user_name
		else:
			broadcast_message = "root user can't be deleted"
		util.redirect(req,"/bin/root.py/user_management?broadcast_message=%s"%broadcast_message)	    
	except GeneralBusinessErr,e:
		return genErrorPage(errmsg='%s'%e.getErrMsg(),errurl=e.getURL())
	except Exception,e:
		return genErrorPage( errmsg = "%s" % e.args )
	except:
		return genErrorPage()

def display_user_details(req,**params):
	"""displays a page of details for a single user"""
	page = Page(req, is_root_page=True, superuser_only=True)
	user_id = params['user_id']
	user_name = params['user_name']
	user_right_list = db_tools.exec_action(page.app_db.cursor(), 'get_user_rights', ['AND a.user_id=%s'%user_id,''])
	user_right_str = ''
	if user_right_list:
		for user_right in user_right_list:
			user_right_str += "<a href='/bin/root.py/display_company_details?company_id="+str(user_right['company_id'])+"'>"+user_right['company_name']+"</a>&nbsp;&nbsp;|&nbsp;&nbsp;<a href='/bin/root.py/display_group_details?group_id="+str(user_right['group_id'])+"'>"+user_right['group_name']+'</a><br/><br/>'	    
	page.content = {
		'user_right_str':user_right_str,
		'user_name':user_name,
		'user_id':user_id
	}
	list_setup={
		'classid':{'main':'title title2','detail':'table'},
		'template_dir':info.getTemplateDir(req),
		'detail':{'title':['User Id','User Name','User Rights'],'value':[page.content['user_id'],page.content['user_name'],page.content['user_right_str'] ]},
		'main':' ', #space required for formatting
	}
	ulist = form.user_div(**list_setup)
	page.content['ulist']=ulist
	page.setup = {
		'breadcrumbs':['root','users',user_name.capitalize() + ' - Details'],
		'menu_title':'Root Menu',
		'menu':['users!','groups','grants','spacer','companies'],
		'content_title':user_name.capitalize() + ' - Details',
		'template':'user_details.opt'
	}
	page.info = {
		'query_string':info.query_string,
	}
	return page.render()

def company_management(req,**params):
	"""displays a list of managed companies, with some links"""
	page = Page(req, is_root_page=True, superuser_only=True)
	broadcast_message = params.has_key('broadcast_message') and params['broadcast_message'] or ''
	companyset=db_tools.exec_action(page.app_db.cursor(),'app_get_all_company')
	companymap={}	    
	for company in companyset:
		if companymap.has_key(company['top_id']):
			companymap[company['top_id']].append(company)
		else:
			companymap[company['top_id']]=[]
			companymap[company['top_id']].append(company)
	tree=""
	for company in companymap[0]:
		tree+=printCompany(companymap,company)
	page.content = {
		'tree':tree,
		'broadcast_message':broadcast_message
	}
	page.setup = {
		'breadcrumbs':['root','Modify Company Details'],
		'menu_title':'Root Menu',
		'menu':['users','groups','grants','spacer','companies!'],
		'content_title':'Modify Company Details',
		'broadcast_message':broadcast_message,
		'template':'company_manage.opt',
		'form':True
	}
	page.info = {
		'query_string':info.query_string
	}
	return page.render()

def display_company_details(req,**params):
	"""displays details for a single managed company"""
	page = Page(req, is_root_page=True, superuser_only=True)
	broadcast_message = params.has_key('broadcast_message') and params['broadcast_message'] or ''
	company_id = params['company_id']
	company_details = db_tools.exec_action(page.app_db.cursor(),'app_get_company_info',[company_id])
	if not company_details:
		util.redirect(req,"/bin/root.py/company_management?broadcast_message=No data for this company") 
	company_details = company_details[0]
	#dbconn = psycopg2.connect('host=%s dbname=%s user=%s password=%s' %(company_details['db_ip'], company_details['db_name'], app_db_user,app_db_passwd))
	dbconn = get_app_connection(host=company_details['db_ip'],dbname=company_details['db_name'])
	dbc = dbconn.cursor()
	this_entity_id = db_tools.exec_action(dbc,'app_get_company_entity_id')[0]['code_value']
	this_address_id = db_tools.exec_action(dbc,'app_get_company_address_id')[0]['code_value']
	abn = db_tools.exec_action(dbc,'app_get_abn_info',[this_entity_id])	
	if abn:
		abn = abn[0]['detail']
	else:
		abn=''
	abn_label = db_tools.exec_action(dbc,'get_sys_code',[info.BUSINESS_NUM_LABEL])[0]['code_value']
	address_details = db_tools.exec_action(dbc,'get_address_info',[this_address_id])[0]
	address_details = utils.get_formatted_address(address_details)
	logo_url=None
	if company_details['logo_url']:
		logo_url =  company_details['logo_url']
	dbconn.close()
	page.content = {
		'abn':abn,
		'abn_label': abn_label,
		'company_details':company_details,
		'address_details':address_details,
		'broadcast_message':broadcast_message,
		'logo_url':logo_url
	}		  
	page.setup = {
		'breadcrumbs':['root','companies',company_details['name'] + ' - Details'],
		'menu_title':'Root Menu',
		'menu':['users','groups','grants','spacer','companies!'],
		'content_title':company_details['name'] + ' - Details',
		'broadcast_message':broadcast_message,	  
		'template':'company_details.opt'
	}
	page.info = {
		'query_string':info.query_string
	}
	return page.render()

def edit_company_details_page(req,**params):
	"""displays a form for editing a managed company's details"""
	page = Page(req, is_root_page=True, superuser_only=True)
	company_id = params['company_id']
	company_details = db_tools.exec_action(page.app_db.cursor(),'app_get_company_info',[company_id])
	if not company_details:
		util.redirect(req,"/bin/root.py/company_management?broadcast_message=no this company!") 
	company_details = company_details[0]
	#dbconn = psycopg2.connect('host=%s dbname=%s user=%s password=%s' %(company_details['db_ip'], company_details['db_name'], app_db_user,app_db_passwd))
	dbconn = get_app_connection(host=company_details['db_ip'],dbname=company_details['db_name'])
	dbc = dbconn.cursor()
	this_entity_id = db_tools.exec_action(dbc,'app_get_company_entity_id')[0]['code_value']
	this_address_id = db_tools.exec_action(dbc,'app_get_company_address_id')[0]['code_value']
	abn = db_tools.exec_action(dbc,'app_get_abn_info',[this_entity_id])
	if abn:
		abn = abn[0]['detail']
	else:
		abn = ''
	abn_label = db_tools.exec_action(dbc,'get_sys_code',[info.BUSINESS_NUM_LABEL])[0]['code_value']
	address_details = db_tools.exec_action(dbc,'get_address_info',[this_address_id])[0]
	logo_url=None
	inv_terms=db_tools.exec_action(dbc,'get_default_inv_terms')[0]['code_value']
	if company_details['logo_url']:
		logo_url =  company_details['logo_url']
	dbconn.close()
	page.content = {
		'abn':abn,
		'abn_label': abn_label,
		'company_details':company_details,
		'address_details':address_details,
		'original_page_load':datetime.datetime.today(),
		'logo_url':logo_url,
		'inv_terms':inv_terms
	}
	page.setup = {
		'breadcrumbs':['root','companies',company_details['name'] + ' - Edit'],
		'menu_title':'Root Menu',
		'menu':['users','groups','grants','spacer','companies!'],
		'content_title':company_details['name'] + ' - Edit',	    
		'template':'edit_company_details.opt',
		'form':True
	}
	page.info = {
		'query_string':info.query_string
	}
	return page.render() 

def edit_company_details(req,**params):
	"""actually updates a managed company's details, redirects to company details page"""
	try:
		page=Page(req,is_root_page=True,superuser_only=True,app_db=True)
		conn=page.app_db
		cursor=conn.cursor()		
##		session = Session.Session(req)
##		check_session(req,session)	
##		conn = get_app_connection( )
##		cursor = conn.cursor()
##		if not user_is_root(session,cursor): raise GeneralBusinessErr('access_forbit','/bin/login.py/login_out')
		company_id = params['company_id']
		default_inv_terms=params['inv_terms']
		company_details = db_tools.exec_action(cursor,'app_get_company_info',[company_id])
		if not company_details:
			util.redirect(req,"/bin/root.py/display_company_details?company_id=%s&amp;broadcast_message=Couldn't retrieve details for company %s. Nothing was updated" % (company_id,company_details['name']))
		company_details = company_details[0]
		#dbconn = psycopg2.connect('host=%s dbname=%s user=%s password=%s' %(company_details['db_ip'], company_details['db_name'], app_db_user,app_db_passwd))
		dbconn = get_app_connection(host=company_details['db_ip'],dbname=company_details['db_name'])
		dbc = dbconn.cursor()
		root_path = req.document_root()
		logo_url = None
		if params.has_key('logo') and params['logo']!='':
			import os
			fileext = os.path.splitext(params['logo'].filename)[1]	     
			filevalue = params['logo'].file.read()
			refilename = utils.shorten(company_details['name'])+'_logo'+fileext
			filepath = info.COMPANY_ROOT_PATH + refilename
			utils.save_upload_file(info.COMPANY_ROOT_PATH,filepath,filevalue)
			logo_url = os.path.join(info.COMPANY_VIRTUAL_PATH,refilename)
		if logo_url:
			cursor.execute("UPDATE system.company SET logo_url='%s',name='%s' WHERE id=%s"%(logo_url,params['company_name'],company_id))
		else:
			cursor.execute("UPDATE system.company SET name='%s' WHERE id=%s"%(params['company_name'],company_id))
		conn.commit()
		conn.close()
		this_entity_id = db_tools.exec_action(dbc,'app_get_company_entity_id')[0]['code_value']
		this_address_id = db_tools.exec_action(dbc,'app_get_company_address_id')[0]['code_value']
		dbc.execute("SELECT system.safe_update('system.contact_detail', 'type = ''abn'' AND entity_id = %s', 'detail=''%s''', '%s')"% (this_entity_id, params['business_reg_no'],params['original_page_load']))
		if params['line2'].strip():
			dbc.execute("SELECT system.safe_update('system.address', 'id = %s', 'line1=''%s'',line2=''%s'',suburb=''%s'',region=''%s'',code=''%s'',country=''%s''', '%s')" % (this_address_id,params['line1'],params['line2'],params['suburb'],params['region'],params['code'],params['country'],params['original_page_load']))
		else:
			dbc.execute("SELECT system.safe_update('system.address', 'id = %s', 'line1=''%s'',line2=null,suburb=''%s'',region=''%s'',code=''%s'',country=''%s''', '%s')" % (this_address_id,params['line1'],params['suburb'],params['region'],params['code'],params['country'],params['original_page_load']))
		inv_terms=db_tools.exec_action(dbc,'get_default_inv_terms')[0]['code_value']
		if default_inv_terms !='' and inv_terms!=default_inv_terms:
			dbc.execute("SELECT system.safe_update('system.sys_code','code_id=''default_inv_terms''','code_value=''%s''','%s')"%(default_inv_terms,params['original_page_load']))
		dbconn.commit()
		dbconn.close()
		util.redirect(req,"/bin/root.py/display_company_details?company_id=%s&amp;broadcast_message=Company details updated" % company_id)
	except GeneralBusinessErr,e:
		return genErrorPage(errmsg='%s'%e.getErrMsg(),errurl=e.getURL())
	except Exception,e:
		return genErrorPage( errmsg = "%s" % e.args )
	except:
		return genErrorPage()

def mypage(req,**params):
	"""displays the my preferences page, which doesn't have a lot on it at present"""
	page = Page(req, is_root_page=True)
	if len(page.session['company_group_list'])<=1 and not user_is_root(page.session, page.app_db.cursor()):
		page['is_root_page']=False		
	broadcast_message = params.has_key('broadcast_message') and params['broadcast_message'] or ''
	page.content = {
		'user_name':page.session['login_user_name'],
		'user_skin':page.session['skin'],
		'user_right_list':db_tools.exec_action(page.app_db.cursor(), 'get_user_rights', ['AND a.user_id=%s' % page.session['login_user_id'],''])
	}
	list_setup={
		'classid':{'main':'title title2','detail':'table'},
		'template_dir':info.getTemplateDir(req),
		'detail':{'title':['Login User','User Skin'],'value':[page.content['user_name'] ,page.content['user_skin']]},
		'main':'My Details',
	}
	ulist=form.user_div(**list_setup)
	page.content['ulist']=ulist
	page.setup = {
		'breadcrumbs':['root','My Prefs'],
		'menu_title':'User Config',
		'menu':['passwd','skin','portal'],
		'content_title':'My Preferences',	    
		'broadcast_message':broadcast_message,
		'template':'mypage.opt'
	}
	if len(page.session['company_group_list'])<=1 and not user_is_root(page.session, page.app_db.cursor()):
		page.setup['breadcrumbs']=['My Prefs']
	page.info = {
		'query_string':info.query_string,
	}
	return page.render()

def change_password_page(req,**params):
	"""displays a form for changing the current user's password"""
	page = Page(req, is_root_page=True)
	if len(page.session['company_group_list'])<=1 and not user_is_root(page.session, page.app_db.cursor()):
		page['is_root_page']=False
	broadcast_message = params.has_key('broadcast_message') and params['broadcast_message'] or ''
	user_id = page.session['login_user_id']
	user_name = page.session['login_user_name']
	form_setup={
		'id':'user_details',
		'action':'/bin/root.py/change_password',
		'onsubmit':'check_form()',
		'fieldsets':[{
			'id':None,
			'legend':'User Details',
			'classid':'form',
			'items':[		 
				{'type':'password','label':'Old Password','id':'old_user_pwd','name':'old_user_pwd','classid':'required'},
				{'type':'password','label':'New Password','id':'user_pwd','name':'user_pwd','classid':'required'},
				{'type':'password','label':'Retype Password','id':'config_user_pwd','name':'config_user_pwd','classid':'required'}		   
			]
		}],
		'template_dir':info.getTemplateDir(req),
		'form_bottom': {'type':'div','id':None,'classid':'submit','style':None,'items':[{'type':'submit','id':'submit','name':'submit','value':'Change'},{'type':'hidden','name':'user_name','value':user_name},{'type':'hidden','name':'user_id','value':user_id}]},
	}
	uform = form.user_form(**form_setup)
	page.content = {
		'uform':uform,
		'user_id':user_id,
		'user_name':user_name,
	}
	page.setup = {
		'breadcrumbs':['root','myprefs','Change Password'],
		'menu_title':'User Config',
		'menu':['passwd!','skin','portal'],
		'content_title':'Change Password',	    
		'broadcast_message':broadcast_message,
		'template':'mypage_change_pwd.opt',
		'form':True,
		'javascript':True
	}
	if len(page.session['company_group_list'])<=1 and not user_is_root(page.session, page.app_db.cursor()):
		page.setup['breadcrumbs']=['root','Change Password']
	page.info = {
		'query_string':info.query_string
	}
	return page.render()

def change_password(req,**params):
	"""actually changes the current user's password, redirects to my preferences page"""
	import phunc.user as user
	try:
		page=Page(req,is_root_page=True,app_db=True)
##		session = Session.Session(req)
##		check_session(req,session)	
##		conn = get_app_connection()
##		cursor = conn.cursor()
##		if not (params.has_key('user_id') and params['user_id'].strip() and params.has_key('user_name') and params['user_name'].strip()):
##			raise GeneralBusinessErr('user_not_login','/bin/login.py/login_out')
		user_id = params['user_id']
		user_name = params['user_name']
		broadcast_message = user.changePasswd(page.app_db.cursor(),user_name,params['old_user_pwd'],params['user_pwd'])
		page.app_db.commit()
		util.redirect(req,"/bin/root.py/mypage?broadcast_message=%s"%broadcast_message)
	except GeneralBusinessErr,e:
		return genErrorPage(errmsg='%s'%e.getErrMsg(),errurl=e.getURL())
	except Exception,e:
		return genErrorPage( errmsg = "%s" % e.args )
	except:
		return genErrorPage()

def change_skin_page(req,**params):
	"""displays a simple form for changing the current users's skin preference"""
	page = Page(req, is_root_page=True)
	if len(page.session['company_group_list'])<=1 and not user_is_root(page.session, page.app_db.cursor()):
		page['is_root_page']=False
	broadcast_message = params.has_key('broadcast_message') and params['broadcast_message'] or ''
	user_id = page.session['login_user_id']	
	user_skin = page.session['skin']       
	page.content = {
		'user_id':user_id,
		'user_skin':user_skin
	}
	page.setup = {
		'breadcrumbs':['root','myprefs','Change Skin'],
		'menu_title':'User Config',
		'menu':['passwd','skin!','portal'],
		'content_title'	:'Change Skin',	    
		'broadcast_message':broadcast_message,
		'template':'mypage_change_skin.opt',
		'form':True
	}
	if len(page.session['company_group_list'])<=1 and not user_is_root(page.session, page.app_db.cursor()):
		page.setup['breadcrumbs']=['root','Change Skin']
	page.info = {
		'query_string':info.query_string
	}
	return page.render()

def change_skin(req,**params):
	"""actually change the current user's skin preference, redirects to my preferences page"""
	try:
		page=Page(req,is_root_page=True,app_db=True)
		user_id = params['user_id']
		user_skin = params['skin']
		db_tools.exec_action(page.app_db.cursor(), 'change_skin', [user_skin,user_id])
		page.session['skin'] = params['skin']
		page.session.save()
		page.app_db.commit()
		util.redirect(req,"/bin/root.py/change_skin_page?broadcast_message=Skin preference updated")
	except GeneralBusinessErr,e:
	    return genErrorPage(errmsg='%s'%e.getErrMsg(),errurl=e.getURL())
	except Exception,e:
	    return genErrorPage( errmsg = "%s" % e.args )
	except:
	    return genErrorPage()
def change_default_portal_page(req,**params):
	"""displays a simple form for changing the current users's default login portal preference"""
	page = Page(req, is_root_page=True)
	portal_list=[]	
	for company in page.session['company_group_list']:
		access_portals = db_tools.exec_action(page.app_db.cursor(), 'get_role_by_group', [int(company['group_id'])])			
		for portal in access_portals:
			if portal not in portal_list and portal['name']!='report':
				portal_list.append(portal)
	default_orders = page.session['portal_order']	
	if len(page.session['company_group_list'])<=1 and not user_is_root(page.session, page.app_db.cursor()):
		page['is_root_page']=False
		
	broadcast_message = params.has_key('broadcast_message') and params['broadcast_message'] or ''
	page.content = {
		'user_id':page.session['login_user_id'],
		'portal_list':portal_list,
		'order_list':[i+1 for i in range(len(portal_list))],
		'default_orders':default_orders,
	}
	page.setup = {
		'breadcrumbs':['root','myprefs','Change Default Portal'],
		'menu_title':'User Config',
		'menu':['passwd','skin','portal!'],
		'content_title'	:'Change Portal Order',	    
		'broadcast_message':broadcast_message,
		'template':'mypage_change_portal.opt',
		'form':True,
		'javascript':True
	}
	if len(page.session['company_group_list'])<=1 and not user_is_root(page.session, page.app_db.cursor()):
		page.setup['breadcrumbs']=['root','Change Default Portal']
	page.info = {
		'query_string':info.query_string
	}
	return page.render()
def change_portal(req,**params):
	"""displays a simple form for changing the current users's default login portal preference"""
	page = Page(req, is_root_page=True)
	portal_list=[]	
	for company in page.session['company_group_list']:
		access_portals = db_tools.exec_action(page.app_db.cursor(), 'get_role_by_group', [int(company['group_id'])])			
		for portal in access_portals:
			if portal not in portal_list and portal['name']!='report':
				portal_list.append(portal)
	portal_order_str=''	
	for i in [i+1 for i in range(len(portal_list))]:
		for portal in portal_list:
			if int(params[portal['name']].split('-')[1])==i:
				portal_order_str += str(portal['id'])+','
	if portal_order_str:
		portal_order_str=portal_order_str[:-1]
	db_tools.exec_action(page.app_db.cursor(), 'update_user_portal_order', ['{'+portal_order_str+'}',page.session['login_user_id']])
	page.app_db.commit()
	page.session['portal_order'] = tuple([int(i) for i in portal_order_str.split(',')])
	page.session.save()
	broadcast_message = "The Portal order defined"
	util.redirect(req,"/bin/root.py/change_default_portal_page?broadcast_message=%s"%broadcast_message)
